﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBHelper;
using System.Data.SqlClient;
using System.Data;
using BusinessLayer.WorldArtPrice.Entities;
using BusinessLayer.WorldArtPrice.Models;

namespace BusinessLayer.WorldArtPrice
{
    public class PriceBL
    {        
        private readonly DbHelper _db;

        public PriceBL()
        {
            _db = new DbHelper(new DbConfig().DbWorldArtPriceConnectionString);         
        }

        public void GetCountByKeyWord(string keyWord, DateTime fromDate, DateTime toDate, int country, int category, out int totalRecord)
        {
            var oCommand = new SqlCommand("sp_Price_Select_Count_By_KeyWord") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@FromDate", fromDate));
            oCommand.Parameters.Add(new SqlParameter("@ToDate", toDate));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            oCommand.Parameters.Add(new SqlParameter("@Category", category));
            var output = new SqlParameter("@TotalRecord", SqlDbType.Int) { Direction = ParameterDirection.Output };
            oCommand.Parameters.Add(output);                                              
            _db.ExecuteNonQuery(oCommand);
            totalRecord = (int)output.Value;            
        }

        public List<Prices> Search(string keyWord, DateTime fromDate, DateTime toDate, int country, int category, int pageIndex, int pageSize)
        {
            var oCommand = new SqlCommand("sp_Price_Search") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@FromDate", fromDate));
            oCommand.Parameters.Add(new SqlParameter("@ToDate", toDate));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            oCommand.Parameters.Add(new SqlParameter("@Category", category));   
            oCommand.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            oCommand.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            var list = _db.GetList<Prices>(oCommand);            
            return list;
        }

        public void GetCountByArtistId(int artistId, out int totalRecord)
        {
            var oCommand = new SqlCommand("sp_Price_Select_Count_By_ArtistId") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ArtistId", artistId));            
            var output = new SqlParameter("@TotalRecord", SqlDbType.Int) { Direction = ParameterDirection.Output };
            oCommand.Parameters.Add(output);
            _db.ExecuteNonQuery(oCommand);
            totalRecord = (int)output.Value;
        }

        public List<Prices> GetByArtistId(int artistId, int pageIndex, int pageSize)
        {
            var oCommand = new SqlCommand("sp_Price_Select_By_ArtistId") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ArtistId", artistId));            
            oCommand.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            oCommand.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            var list = _db.GetList<Prices>(oCommand);
            return list;
        }

        public Prices GetById(int id)
        {
            var oCommand = new SqlCommand("sp_Price_Select_By_Id") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@Id", id));
            var list = _db.GetList<Prices>(oCommand);
            return list.FirstOrDefault();
        }

        public void Insert(Prices price, out int id)
        {
            var oCommand = new SqlCommand("sp_Price_Insert") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ArtistId", price.ArtistId));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", price.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@ItemType", price.ItemType));
            oCommand.Parameters.Add(new SqlParameter("@ItemTypeDescription", price.ItemTypeDescription));
            oCommand.Parameters.Add(new SqlParameter("@Height", price.Height));
            oCommand.Parameters.Add(new SqlParameter("@Width", price.Width));
            oCommand.Parameters.Add(new SqlParameter("@Depth", price.Depth));
            oCommand.Parameters.Add(new SqlParameter("@Measurement", price.Measurement));
            oCommand.Parameters.Add(new SqlParameter("@Signed", price.Signed));
            oCommand.Parameters.Add(new SqlParameter("@Stamped", price.Stamped));
            oCommand.Parameters.Add(new SqlParameter("@Inscribed", price.Inscribed));
            oCommand.Parameters.Add(new SqlParameter("@Dating", price.Dating));
            oCommand.Parameters.Add(new SqlParameter("@Lotnumber", price.Lotnumber));
            oCommand.Parameters.Add(new SqlParameter("@AuctionTime", price.AuctionTime));
            oCommand.Parameters.Add(new SqlParameter("@AuctionHouse", price.AuctionHouse));
            oCommand.Parameters.Add(new SqlParameter("@AuctionHouseSublocation", price.AuctionHouseSublocation));
            oCommand.Parameters.Add(new SqlParameter("@Auctionnumber", price.Auctionnumber));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedFrom", price.EstimatedFrom));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedTo", price.EstimatedTo));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedCurrency", price.EstimatedCurrency));
            oCommand.Parameters.Add(new SqlParameter("@Price", price.Price));
            oCommand.Parameters.Add(new SqlParameter("@PriceCurrency", price.PriceCurrency));
            oCommand.Parameters.Add(new SqlParameter("@PriceInUS", price.PriceInUS));
            oCommand.Parameters.Add(new SqlParameter("@Premium", price.Premium));
            oCommand.Parameters.Add(new SqlParameter("@Unsold", price.Unsold));
            oCommand.Parameters.Add(new SqlParameter("@Edition", price.Edition));
            oCommand.Parameters.Add(new SqlParameter("@Foundry", price.Foundry));
            oCommand.Parameters.Add(new SqlParameter("@Impression", price.Impression));
            oCommand.Parameters.Add(new SqlParameter("@DateCreated", price.DateCreated)); 
            var output = new SqlParameter("@ID", SqlDbType.Int) { Direction = ParameterDirection.Output };
            oCommand.Parameters.Add(output);
            _db.ExecuteNonQuery(oCommand);
            id = (int)output.Value;
        }

        public void Update(Prices price)
        {
            var oCommand = new SqlCommand("sp_Price_Update") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ID", price.ID));
            oCommand.Parameters.Add(new SqlParameter("@ArtistId", price.ArtistId));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", price.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@ItemType", price.ItemType));
            oCommand.Parameters.Add(new SqlParameter("@ItemTypeDescription", price.ItemTypeDescription));
            oCommand.Parameters.Add(new SqlParameter("@Height", price.Height));
            oCommand.Parameters.Add(new SqlParameter("@Width", price.Width));
            oCommand.Parameters.Add(new SqlParameter("@Depth", price.Depth));
            oCommand.Parameters.Add(new SqlParameter("@Measurement", price.Measurement));
            oCommand.Parameters.Add(new SqlParameter("@Signed", price.Signed));
            oCommand.Parameters.Add(new SqlParameter("@Stamped", price.Stamped));
            oCommand.Parameters.Add(new SqlParameter("@Inscribed", price.Inscribed));
            oCommand.Parameters.Add(new SqlParameter("@Dating", price.Dating));
            oCommand.Parameters.Add(new SqlParameter("@Lotnumber", price.Lotnumber));
            oCommand.Parameters.Add(new SqlParameter("@AuctionTime", price.AuctionTime));
            oCommand.Parameters.Add(new SqlParameter("@AuctionHouse", price.AuctionHouse));
            oCommand.Parameters.Add(new SqlParameter("@AuctionHouseSublocation", price.AuctionHouseSublocation));
            oCommand.Parameters.Add(new SqlParameter("@Auctionnumber", price.Auctionnumber));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedFrom", price.EstimatedFrom));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedTo", price.EstimatedTo));
            oCommand.Parameters.Add(new SqlParameter("@EstimatedCurrency", price.EstimatedCurrency));
            oCommand.Parameters.Add(new SqlParameter("@Price", price.Price));
            oCommand.Parameters.Add(new SqlParameter("@PriceCurrency", price.PriceCurrency));
            oCommand.Parameters.Add(new SqlParameter("@PriceInUS", price.PriceInUS));
            oCommand.Parameters.Add(new SqlParameter("@Premium", price.Premium));
            oCommand.Parameters.Add(new SqlParameter("@Unsold", price.Unsold));
            oCommand.Parameters.Add(new SqlParameter("@Edition", price.Edition));
            oCommand.Parameters.Add(new SqlParameter("@Foundry", price.Foundry));
            oCommand.Parameters.Add(new SqlParameter("@Impression", price.Impression));
            oCommand.Parameters.Add(new SqlParameter("@DateCreated", price.DateCreated));
            
            _db.ExecuteNonQuery(oCommand);
            
        }

        public void Delete(int id)
        {
            var oCommand = new SqlCommand("sp_Price_Delete") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ID", id));

            _db.ExecuteNonQuery(oCommand);
        }
        public int UpdateArtistIdByIds(string lstofids,int artistId)
        {
            int result = -1;


            if (!String.IsNullOrEmpty(lstofids))
            {
                try
                {
                    var oCommand = new SqlCommand(String.Format("UPDATE Price SET ArtistId = {0} FROM Artists WHERE ID IN ({1});",artistId, lstofids)) { CommandType = CommandType.Text };
                    result = _db.ExecuteNonQuery(oCommand);
                    return result;
                }
                catch (Exception ex)
                {

                }
            }
            return result;
        }
    }
}
